from .config import app, dblock
from fastapi import Request, Query
from dataclasses import asdict

@app.get("/api/grade/list")
async def get_grade_list():
    with dblock() as db:
        db.execute("""
        SELECT g.stu_sn, g.cou_sn, 
            s.stu_name as stu_name, 
            c.cou_name as cou_name, 
            g.grade 
        FROM course_grade as g
            INNER JOIN student as s ON g.stu_sn = s.sn
            INNER JOIN course as c  ON g.cou_sn = c.sn
        ORDER BY g.stu_sn, g.cou_sn;        
        """)
        data = list(db)

    return data

@app.post("/api/grade/batch")
async def batch_grade_entry(request: Request):
    data = await request.json()
    class_sn = data.get('class_sn')
    grades = data.get('grades', {})
    # 先查出该班次下所有学生和课程
    with dblock() as db:
        db.execute("SELECT stu_sn, class_sn FROM elective WHERE class_sn=%(class_sn)s", {"class_sn": class_sn})
        elective_rows = list(db)
        for row in elective_rows:
            stu_sn = row.stu_sn
            grade = grades.get(str(stu_sn)) or grades.get(stu_sn)
            if grade is not None and grade != "":
                # 假设每个班次只有一个课程，查出 cou_sn
                db.execute("SELECT course_sn FROM class WHERE sn=%(class_sn)s", {"class_sn": class_sn})
                cou_row = db.fetchone()
                cou_sn = cou_row.course_sn if cou_row else None
                if cou_sn:
                    db.execute(
                        """
                        INSERT INTO course_grade (stu_sn, cou_sn, grade)
                        VALUES (%(stu_sn)s, %(cou_sn)s, %(grade)s)
                        ON CONFLICT (stu_sn, cou_sn) DO UPDATE SET grade=EXCLUDED.grade
                        """,
                        {"stu_sn": stu_sn, "cou_sn": cou_sn, "grade": float(grade)}
                    )
    return {"success": True, "count": len(grades)}

@app.get("/api/grade/one")
async def get_grade_one(stu_sn: int, cou_sn: int):
    with dblock() as db:
        db.execute(
            """
            SELECT g.stu_sn, g.cou_sn, s.name as stu_name, c.name as cou_name, g.grade
            FROM course_grade as g
                INNER JOIN student as s ON g.stu_sn = s.sn
                INNER JOIN course as c  ON g.cou_sn = c.sn
            WHERE g.stu_sn=%(stu_sn)s AND g.cou_sn=%(cou_sn)s
            """,
            {"stu_sn": stu_sn, "cou_sn": cou_sn}
        )
        row = db.fetchone()
        if row:
            return dict(row)
        else:
            return {"stu_sn": stu_sn, "cou_sn": cou_sn, "grade": None}

@app.post("/api/grade/one")
async def post_grade_one(request: Request):
    data = await request.json()
    stu_sn = data.get("stu_sn")
    cou_sn = data.get("cou_sn")
    grade = data.get("grade")
    semester = data.get("semester")
    if not (stu_sn and cou_sn):
        return {"error": "stu_sn和cou_sn为必填"}
    with dblock() as db:
        db.execute(
            """
            INSERT INTO course_grade (stu_sn, cou_sn, grade, semester)
            VALUES (%(stu_sn)s, %(cou_sn)s, %(grade)s, %(semester)s)
            ON CONFLICT (stu_sn, cou_sn, semester) DO UPDATE SET grade=EXCLUDED.grade
            """,
            {"stu_sn": stu_sn, "cou_sn": cou_sn, "grade": float(grade) if grade != "" and grade is not None else None, "semester": semester}
        )
    return {"success": True}

@app.delete("/api/grade/one")
async def delete_grade_one(request: Request):
    data = await request.json()
    stu_sn = data.get("stu_sn")
    cou_sn = data.get("cou_sn")
    if not (stu_sn and cou_sn):
        return {"error": "stu_sn和cou_sn为必填"}
    with dblock() as db:
        db.execute(
            "DELETE FROM course_grade WHERE stu_sn=%(stu_sn)s AND cou_sn=%(cou_sn)s",
            {"stu_sn": stu_sn, "cou_sn": cou_sn}
        )
    return {"success": True}

@app.get("/api/grade/query")
def query_grades(course_sn: int = Query(...), class_sn: int = Query(None), semester: str = Query(None)):
    sql = """
        SELECT g.stu_sn, s.stu_name as stu_name, g.grade, cl.name as class_name, g.semester
        FROM course_grade g
        JOIN student s ON g.stu_sn = s.sn
        JOIN class cl ON s.class_sn = cl.sn
    """
    where = []
    params = {}
    if course_sn:
        where.append("g.cou_sn = %(course_sn)s")
        params["course_sn"] = course_sn
    if class_sn:
        where.append("s.class_sn = %(class_sn)s")
        params["class_sn"] = class_sn
    if semester:
        where.append("g.semester = %(semester)s")
        params["semester"] = semester
    if where:
        sql += " WHERE " + " AND ".join(where)
    with dblock() as db:
        db.execute(sql, params)
        data = [asdict(row) for row in db]
    return data
